import random

import psycopg2
import time


class SQL_method:
    def __init__(self):
        self.conn = psycopg2.connect(database="lab3", user="testuser", password="test123", host="yourhost",
                                     port="5432")
        self.cur = self.conn.cursor()

    def student_query(self):
        start = time.perf_counter()
        self.cur.execute("SELECT * FROM student ORDER BY sid ASC")
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        sid = []
        name = []
        phone = []
        did = []
        for row in rows:
            sid.append(row[0])
            name.append(row[1])
            phone.append(row[2])
            did.append(row[3])
        return sid, name, phone, did, exec_time

    def student_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO student VALUES (%s,%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE student SET sname = %s, sphone = %s, did = %s where sid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM student where sid = %s', [data[0]])

    def course_query(self):
        start = time.perf_counter()
        self.cur.execute('SELECT coid,cname,credits,loc,room,start_time,end_time,did,thid '
                         'FROM course ORDER BY coid ASC')
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        coid = []
        name = []
        c_credits = []
        loc = []
        room = []
        start_time = []
        end_time = []
        did = []
        thid = []
        for row in rows:
            coid.append(row[0])
            name.append(row[1])
            c_credits.append(str(row[2]))
            loc.append(row[3])
            room.append(row[4])
            start_time.append(str(row[5]))
            end_time.append(str(row[6]))
            did.append(row[7])
            thid.append(row[8])
        return coid, name, c_credits, loc, room, start_time, end_time, did, thid, exec_time

    def course_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO course VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE course SET cname = %s, credits = %s, loc = %s, room = %s,'
                             'start_time = %s, end_time = %s, did = %s, thid = %s '
                             'where coid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM course where coid = %s', [data[0]])

    def teacher_query(self):
        """

        :return: thid, name, salary, address, did
        """
        start = time.perf_counter()
        self.cur.execute("SELECT * FROM teacher ORDER BY thid ASC")
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        thid = []
        name = []
        salary = []
        address = []
        did = []
        for row in rows:
            thid.append(row[0])
            name.append(row[1])
            salary.append(str(row[2]))
            address.append(row[3])
            did.append(row[4])
        return thid, name, salary, address, did, exec_time

    def teacher_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO teacher VALUES (%s,%s,%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE teacher SET tname = %s, salary = %s, address = %s, did = %s '
                             'where thid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM teacher where thid = %s', [data[0]])

    def project_query(self):
        """

        :return: pid, name, thid
        """
        start = time.perf_counter()
        self.cur.execute("SELECT * FROM project ORDER BY pid ASC")
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        pid = []
        name = []
        thid = []
        for row in rows:
            pid.append(row[0])
            name.append(row[1])
            thid.append(row[2])
        return pid, name, thid, exec_time

    def project_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO project VALUES (%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE project SET pname = %s, thid = %s '
                             'where pid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM project where pid = %s', [data[0]])

    def department_query(self):
        """

        :return: did, name, loc , thid
        """
        start = time.perf_counter()
        self.cur.execute("SELECT * FROM department ORDER BY did ASC")
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        did = []
        name = []
        loc = []
        thid = []
        for row in rows:
            did.append(row[0])
            name.append(row[1])
            loc.append(row[2])
            thid.append(row[3])
        return did, name, loc, thid, exec_time

    def department_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO department VALUES (%s,%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE department SET dname = %s, loc = %s, thid = %s '
                             'where did = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM department where did = %s', [data[0]])

    def contacts_query(self):
        """

        :return: sid, sname, conname, sex, phone
        """
        start = time.perf_counter()
        self.cur.execute(
            'SELECT contacts.sid, student.sname, contacts.conname, contacts.sex, contacts.phone '
            'FROM contacts NATURAL JOIN student ORDER BY sid ASC')
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        sid = []
        sname = []
        conname = []
        sex = []
        phone = []
        for row in rows:
            sid.append(row[0])
            sname.append(row[1])
            conname.append(row[2])
            sex.append(row[3])
            phone.append(row[4])
        return sid, sname, conname, sex, phone, exec_time

    def contacts_operation(self, op, data: list):
        if op == 'INSERT':
            temp = [data[2], data[0], data[4], data[3]]
            self.cur.execute('INSERT INTO contacts VALUES (%s,%s,%s,%s)', temp)
        elif op == 'UPDATE':
            temp = [data[4], data[3], data[0], data[2]]
            self.cur.execute('UPDATE contacts SET phone = %s, sex = %s '
                             'where sid = %s and conname = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM contacts where sid = %s and conname = %s', [data[0], data[2]])

    def classroom_query(self):
        """

        :return: loc, room, capacity
        """
        start = time.perf_counter()
        self.cur.execute('SELECT * from classroom ORDER BY loc ASC')
        end = time.perf_counter()
        exec_time = end - start
        rows = self.cur.fetchall()
        loc = []
        room = []
        capacity = []
        for row in rows:
            loc.append(row[0])
            room.append(row[1])
            capacity.append(str(row[2]))
        return loc, room, capacity, exec_time

    def classroom_operation(self, op, data: list):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO classroom VALUES (%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[2:]
            temp.append(data[0])
            temp.append(data[1])
            self.cur.execute('UPDATE classroom SET capacity = %s'
                             'where loc = %s and room = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM classroom where loc = %s and room = %s', [data[0], data[1]])

    def student_information(self, sid):
        self.cur.execute('select student.sname, student.sphone, student.did, department.dname '
                         'from student natural join department where sid = %s', [sid])
        row = self.cur.fetchall()
        return row[0]

    def chosen_course_query(self, sid):
        self.cur.execute('select course.coid, course.cname, course.credits, course.loc, course.room, teacher.tname '
                         'from take natural join (course join teacher on (course.thid = teacher.thid)) '
                         'where sid = %s'
                         'ORDER BY coid ASC', [sid])
        rows = self.cur.fetchall()
        coid = []
        cname = []
        credits = []
        loc = []
        room = []
        tname = []
        for row in rows:
            coid.append(row[0])
            cname.append(row[1])
            credits.append(str(row[2]))
            loc.append(row[3])
            room.append(str(row[4]))
            tname.append(row[5])
        return coid, cname, credits, loc, room, tname

    def selectable_course_query(self, sid):
        self.cur.execute('select coid, cname, credits, loc, room, teacher.tname '
                         'from course join teacher on (course.thid = teacher.thid) '
                         'where coid not in '
                         '(select coid from take where sid = %s) '
                         'ORDER BY coid ASC', [sid])
        rows = self.cur.fetchall()
        coid = []
        cname = []
        credits = []
        loc = []
        room = []
        tname = []
        for row in rows:
            coid.append(row[0])
            cname.append(row[1])
            credits.append(str(row[2]))
            loc.append(row[3])
            room.append(str(row[4]))
            tname.append(row[5])
        return coid, cname, credits, loc, room, tname

    def take_operation(self, op, data):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO take VALUES (%s,%s)', data)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM take where sid = %s and coid = %s', data)

    def chosen_project_query(self, sid):
        self.cur.execute('select project.pid, project.pname, teacher.tname '
                         'from works natural join project natural join teacher where sid = %s '
                         'ORDER BY pid ASC', [sid])
        rows = self.cur.fetchall()
        pid = []
        pname = []
        tname = []
        for row in rows:
            pid.append(row[0])
            pname.append(row[1])
            tname.append(row[2])
        return pid, pname, tname

    def selectable_project_query(self, sid):
        self.cur.execute('select project.pid, project.pname, teacher.tname '
                         'from project natural join teacher where pid not in '
                         '(select pid from works where sid = %s) ORDER BY pid ASC', [sid])
        rows = self.cur.fetchall()
        pid = []
        pname = []
        tname = []
        for row in rows:
            pid.append(row[0])
            pname.append(row[1])
            tname.append(row[2])
        return pid, pname, tname

    def works_operation(self, op, data):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO works VALUES (%s,%s)', data)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM works where sid = %s and pid = %s', data)

    def teacher_information(self, thid):
        self.cur.execute('select teacher.tname, teacher.address, teacher.did, department.dname '
                         'from teacher join department on (teacher.did = department.did) where teacher.thid = %s',
                         [thid])
        row_teacher = self.cur.fetchall()
        return row_teacher[0]

    def teacher_course_query(self, thid):
        self.cur.execute('select coid, cname, credits, loc, room, start_time, end_time '
                         'from course where thid = %s', [thid])
        rows_course = self.cur.fetchall()
        coid = []
        cname = []
        credits = []
        loc = []
        room = []
        start_time = []
        end_time = []
        for row in rows_course:
            coid.append(row[0])
            cname.append(row[1])
            credits.append(str(row[2]))
            loc.append(row[3])
            room.append(row[4])
            start_time.append(str(row[5]))
            end_time.append(str(row[6]))
        return coid, cname, credits, loc, room, start_time, end_time

    def teacher_project_query(self, thid):
        self.cur.execute('select pid, pname '
                         'from project where thid = %s', [thid])
        rows_project = self.cur.fetchall()
        pid = []
        pname = []
        for row in rows_project:
            pid.append(row[0])
            pname.append(row[1])
        return pid, pname

    def teacher_course_operation(self, op, data):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO course VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE course SET cname = %s, credits = %s, loc = %s, room = %s, '
                             'start_time = %s, end_time = %s, did = %s, thid = %s '
                             'where coid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM course where coid = %s', [data[0]])

    def teacher_project_operation(self, op, data):
        if op == 'INSERT':
            self.cur.execute('INSERT INTO project VALUES (%s,%s,%s)', data)
        elif op == 'UPDATE':
            temp = data[1:]
            temp.append(data[0])
            self.cur.execute('UPDATE project SET pname = %s, thid = %s '
                             'where pid = %s', temp)
        elif op == 'DELETE':
            self.cur.execute('DELETE FROM project where pid = %s', [data[0]])
